package adsi.persistence;

import adsi.domain.*;
import adsi.utils.*;
import java.sql.*;
import java.util.*;
import java.util.logging.*;

/**
 * DataServiceImpl is the implementation of the DataServiceModel interfase.
 */
public class DataServiceImpl implements DataServiceModel {

    private static final DataServiceImpl instance = new DataServiceImpl();
    private Database db;

    private DataServiceImpl() {
        db = Database.getInstance();
    }

    public static DataServiceImpl getInstance() {
        return instance;
    }

    public synchronized long saveEquipment(Equipment e) throws DataServiceException {
        String sql = "";
        String err;
        long[] queryRes;

        try {
            if (e.getID() < 0) {
                sql = "INSERT INTO equipos " +
                        "(TailNumber, FechaAlta, FechaBaja, FechaDisponibilidadEquipo, Disponible)" +
                        "VALUES ('" +
                        e.getTailNumber() + "', " +
                        DateTime.toSql() + ", " +
                        DateTime.toSql(e.getFechaBaja()) + ", " +
                        DateTime.toSql(e.getFechaDisponibilidad()) + ", " +
                        e.isDisponible() + ")";
                queryRes = db.executeUpdate(sql, true);
            } else {
                sql = "UPDATE equipos SET " +
                        "TailNumber = '" + e.getTailNumber() + "' " +
                        "FechaAlta = " + DateTime.toSql(e.getFechaAlta()) + " " +
                        "FechaBaja = " + DateTime.toSql(e.getFechaBaja()) + " " +
                        "FechaDisponibilidadEquipo = " + DateTime.toSql(e.getFechaDisponibilidad().getTime()) + " " +
                        "Disponible = " + e.isDisponible() + " " +
                        "WHERE IDEquipo =" + e.getID();
                queryRes = new long[2];
                queryRes[0] = db.executeUpdate(sql);
                queryRes[1] = e.getID();
            }


            if (queryRes[0] != 1) {
                if (queryRes[0] == 0) {
                    Logger.getLogger("logger").log(Level.WARNING, "Cero registros agregados/modificados.", sql);
                } else {
                    //This condition should never occur, since persistence medium
                    //is responsible of maintaining uniqueness of keys.
                    Logger.getLogger("logger").log(Level.SEVERE, "Más de un registros fue agregado/modificado.", sql);
                }
                throw new DataServiceException("Se insertaron/modificaron " + queryRes[0] + " registros.");
            }
        } catch (DataServiceException ex) {
            err = "Error al grabar registro de equipo " + e.getTailNumber() + ".";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        }

        return queryRes[1];
    }

    public synchronized void deleteEquipment(long ID) throws DataServiceException {
        String sql;
        String err;
        int queryRes;

        try {
            sql = "UPDATE equipos SET Borrado = TRUE WHERE IDEquipo = " + ID;
            queryRes = db.executeUpdate(sql);

            if (queryRes == 0) {
                throw new DataServiceException("Ningún registro fue marcado como borrado.");
            } else {
                //This condition should never occur, since persistence medium
                //is responsible of maintaining uniqueness of keys.
                err = "Más de un registro fue marcado como borrado.";
                Logger.getLogger("logger").log(Level.SEVERE, err, sql);
                throw new DataServiceException(err);
            }
        } catch (DataServiceException ex) {
            err = "Error al marcar como borrado el registro con ID " + ID + ".";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        }
    }

    public synchronized void undeleteEquipment(long ID) throws DataServiceException {
        String sql;
        String err;
        int queryRes;

        try {
            sql = "UPDATE equipos SET Borrado = FALSE WHERE IDEquipo = " + ID;
            queryRes = db.executeUpdate(sql);

            if (queryRes == 0) {
                throw new DataServiceException("Ningún registro fue desmarcado como borrado.");
            } else {
                //This condition should never occur, since persistence medium
                //is responsible of maintaining uniqueness of keys.
                err = "Más de un registro fue desmarcado como borrado.";
                Logger.getLogger("logger").log(Level.SEVERE, err, sql);
                throw new DataServiceException(err);
            }
        } catch (DataServiceException ex) {
            err = "Error al desmarcar como borrado el registro con ID " + ID + ".";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        }
    }

    public synchronized Equipment retrieveEquipment(long ID) throws DataServiceException, EntityException {
        ResultSet rs = null;
        String sql = "";
        Equipment res = null;
        String err;

        try {
            sql = "SELECT * from equipos WHERE IDEquipo = " + ID +
                    " AND Borrado = FALSE";
            rs = db.executeQuery(sql);

            if (rs.next()) {
                res = new Equipment();
                res.setID(rs.getLong(1));
                res.setTailNumber(rs.getString(2));
                res.setFechaAlta(DateTime.DateToCalendar(rs.getDate(3)));
                res.setFechaBaja(DateTime.DateToCalendar(rs.getDate(4)));
                res.setFechaDisponibilidad(DateTime.DateToCalendar(rs.getDate(5)));
                res.setDisponible(rs.getBoolean(6));
                if (rs.next()) {
                    //This condition should never occur, since persistence medium
                    //is responsible of maintaining uniqueness of keys.
                    err = "Existe más de un registro con el ID " + ID + ".";
                    Logger.getLogger("logger").log(Level.SEVERE, err);
                    throw new DataServiceException(err);
                }
            } else {
                throw new DataServiceException("No existe ningún equipo con el ID " + ID + ".");
            }
        } catch (EntityException ex) {
            err = "Error al crear el objeto Equipo con ID " + ID + ".";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        } catch (SQLException ex) {
            err = "Error al recuperar registro de Equipo con ID " + ID + ".";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        } finally {
            err = "Error al cerrar los recursos.";
            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (SQLException ex) {
                    Logger.getLogger("logger").log(Level.WARNING, err, ex);
                }
            }
        }

        return res;
    }

    public synchronized Equipment retrieveEquipment(String tailNumber) throws DataServiceException, EntityException {
        ResultSet rs = null;
        String sql = "";
        Equipment res = null;
        String err;

        try {
            sql = "SELECT * from equipos WHERE TailNumber = " + tailNumber +
                    " AND Borrado = FALSE";
            rs = db.executeQuery(sql);

            if (rs.next()) {
                res = new Equipment();
                res.setID(rs.getLong(1));
                res.setTailNumber(rs.getString(2));
                res.setFechaAlta(DateTime.DateToCalendar(rs.getDate(3)));
                res.setFechaBaja(DateTime.DateToCalendar(rs.getDate(4)));
                res.setFechaDisponibilidad(DateTime.DateToCalendar(rs.getDate(5)));
                res.setDisponible(rs.getBoolean(6));
                if (rs.next()) {
                    //This condition should never occur, since TailNumber is
                    //indicated as unique in the persistent medium.
                    err = "Existe más de un registro con el Tail Number " + tailNumber + ".";
                    Logger.getLogger("logger").log(Level.SEVERE, err);
                    throw new DataServiceException(err);
                }
            } else {
                throw new DataServiceException("No existe ningún equipo con el Tail Number " + tailNumber + ".");
            }
        } catch (EntityException ex) {
            err = "Error al crear el objeto Equipo con Tail Number " + tailNumber + ".";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        } catch (SQLException ex) {
            err = "Error al recuperar Equipo con Tail Number " + tailNumber + ".";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        } finally {
            err = "Error al cerrar los recursos.";
            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (SQLException ex) {
                    Logger.getLogger("logger").log(Level.WARNING, err, ex);
                }
            }
        }

        return res;
    }

    public synchronized List<Equipment> retrieveEquipments() throws DataServiceException, EntityException {
        ResultSet rs = null;
        String sql = "";
        List res = null;
        String err;

        try {
            sql = "SELECT * from equipos WHERE Borrado = FALSE";
            rs = db.executeQuery(sql);
            res = makeListOfEquipments(rs);
        } catch (DataServiceException ex) {
            err = "Error en la conexión con la base de datos.";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        } catch (EntityException ex) {
            err = "Error al crear los objetos Equipo.";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        }

        return res;
    }

    public synchronized Map<Long, String> retrieveEquipmentsIDs() throws DataServiceException {
        ResultSet rs = null;
        String sql = "";
        Map res = null;
        String err;

        try {
            sql = "SELECT * from equipos WHERE Borrado = FALSE";
            rs = db.executeQuery(sql);
            res = new HashMap<Long, String>();

            while (rs.next()) {
                res.put(rs.getLong(1), rs.getString(2));
            }
        } catch (SQLException ex) {
            err = "Error al recuperar los registros de Equipos .";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        } finally {
            err = "Error al cerrar los recursos.";
            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (SQLException ex) {
                    Logger.getLogger("logger").log(Level.WARNING, err, ex);
                }
            }
        }

        return res;
    }

    public synchronized List<Equipment> retrieveEquipmentsLike(String description, String order) {
        ResultSet rs = null;
        String sql;
        List<Equipment> res = new ArrayList();
        String err;

        try {
            sql = "SELECT * from equipos WHERE TailNumber LIKE '%" + description + "%'" +
                    " AND Borrado = FALSE " + getOrderForEquipments(order);
            rs = db.executeQuery(sql);
            res = makeListOfEquipments(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return res;
    }

    private String getOrderForEquipments(String order) {

        if ("created".equals(order)) {
            order = "FechaAlta";
        } else if ("dropped".equals(order)) {
            order = "FechaBaja";
        } else if ("available".equals(order)) {
            order = "FechaDisponibilidadEquipo";
        } else if ("status".equals(order)) {
            order = "Disponible";
        } else {
            order = "TailNumber";
        }

        return " ORDER BY " + order;
    }

    public synchronized List<Equipment> retrieveDeliveredEquipments() throws DataServiceException, EntityException {
        ResultSet rs = null;
        String sql = "";
        List res = null;
        String err;

        try {
            sql = "SELECT * from equipos WHERE Disponible = TRUE" +
                    " AND Borrado = FALSE";
            rs = db.executeQuery(sql);
            res = makeListOfEquipments(rs);
        } catch (DataServiceException ex) {
            err = "Error en la conexión con la base de datos.";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        } catch (EntityException ex) {
            err = "Error al crear los objetos Equipo.";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        } finally {
            err = "Error al cerrar los recursos.";
            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (SQLException ex) {
                    Logger.getLogger("logger").log(Level.WARNING, err, ex);
                }
            }
        }

        return res;
    }

    public synchronized List<Equipment> retrieveEquipmentsInRange(Map<String, Object> parameters) {
        List<Equipment> list = new ArrayList();
        Set<String> keys = parameters.keySet();
        Map<String, String> dbPairs = new HashMap();
        StringBuilder sb = new StringBuilder();
        ResultSet rs;
        String sql;

        dbPairs.put("delivered", "Disponible = ");
        dbPairs.put("deleted", "Borrado = ");
        dbPairs.put("created-from", "FechaAlta >= ");
        dbPairs.put("created-to", "FechaAlta <= ");
        dbPairs.put("dropped-from", "FechaBaja >= ");
        dbPairs.put("dropped-to", "FechaBaja <= ");
        dbPairs.put("available-from", "FechaDisponibilidadEquipo >= ");
        dbPairs.put("available-to", "FechaDisponibilidadEquipo <= ");

        //build conditions
        for (String k : keys) {
            Object o = parameters.get(k);
            if (!k.equals("order") && o != null && !"".equals(o.toString())) {
                sb.append(" AND ");
                sb.append(dbPairs.get(k));
                //Dates
                if (o instanceof Calendar) {
                    sb.append(DateTime.toSql((Calendar) o));
                //Delivered
                } else if (o instanceof Boolean) {
                    sb.append(((Boolean) o).toString());
                }
            }
        }

        try {
            sql = "SELECT * from equipos WHERE Borrado = FALSE " +
                    sb.toString() +
                    getOrderForEquipments((String) parameters.get("order"));
            rs = db.executeQuery(sql);
            list = makeListOfEquipments(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }

    public synchronized long saveLine(Line l) throws DataServiceException {
        String sql = "";
        String err;
        long[] queryRes;

        try {
            if (l.getID() < 0) {
                sql = "INSERT INTO lineas " +
                        "(Descripcion, FechaAlta, FechaBaja, FechaDisponibilidadLinea) " +
                        "VALUES ('" + l.getDescripcion() + "', " +
                        DateTime.toSql(l.getFechaAlta()) + ", " +
                        DateTime.toSql(l.getFechaBaja()) + ", " +
                        DateTime.toSql(l.getFechaDisponibilidadLinea()) + ")";
                queryRes = db.executeUpdate(sql, true);
            } else {
                sql = "UPDATE lineas SET " +
                        "IDLinea = '" + l.getID() + "' " +
                        "FechaAlta = " + DateTime.toSql(l.getFechaAlta()) + " " +
                        "FechaBaja = " + DateTime.toSql(l.getFechaBaja()) + " " +
                        "FechaDisponibilidadLinea = " + DateTime.toSql(l.getFechaDisponibilidadLinea()) + " " +
                        "WHERE IDLinea =" + l.getID();
                queryRes = new long[2];
                queryRes[0] = db.executeUpdate(sql);
                queryRes[1] = l.getID();
            }

            if (queryRes[0] != 1) {
                if (queryRes[0] == 0) {
                    Logger.getLogger("logger").log(Level.WARNING, "Cero registros agregados/modificados.", sql);
                } else {
                    //This condition should never occur, since persistence medium
                    //is responsible of maintaining uniqueness of keys.
                    Logger.getLogger("logger").log(Level.SEVERE, "Más de un registros fue agregado/modificado.", sql);
                }

                throw new DataServiceException("Se insertaron/modificaron " + queryRes[0] + " registros.");
            }

        } catch (DataServiceException ex) {
            err = "Error al grabar registro de equipo " + l.getID() + ".";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        }

        return queryRes[1];
    }

    public synchronized void deleteLine(long ID) throws DataServiceException {
        String sql;
        String err;

        int queryRes;

        try {
            sql = "UPDATE lineas SET borrado = TRUE WHERE IDLinea = " + ID;
            queryRes =
                    db.executeUpdate(sql);

            if (queryRes == 0) {
                throw new DataServiceException("Ningún registro fue marcado como borrado.");
            } else {
                //This condition should never occur, since persistence medium
                //is responsible of maintaining uniqueness of keys.
                err = "Más de un registro fue marcado como borrado.";
                Logger.getLogger("logger").log(Level.SEVERE, err, sql);
                throw new DataServiceException(err);
            }

        } catch (DataServiceException ex) {
            err = "Error al marcar como borrado el registro con ID " + ID + ".";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        }

    }

    public synchronized void undeleteLine(long ID) throws DataServiceException {
        String sql;
        String err;

        int queryRes;

        try {
            sql = "UPDATE lineas SET borrado = FALSE WHERE IDLinea = " + ID;
            queryRes =
                    db.executeUpdate(sql);

            if (queryRes == 0) {
                throw new DataServiceException("Ningún registro fue marcado como borrado.");
            } else {
                //This condition should never occur, since persistence medium
                //is responsible of maintaining uniqueness of keys.
                err = "Más de un registro fue marcado como borrado.";
                Logger.getLogger("logger").log(Level.SEVERE, err, sql);
                throw new DataServiceException(err);
            }

        } catch (DataServiceException ex) {
            err = "Error al marcar como borrado el registro con ID " + ID + ".";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        }

    }

    public synchronized Line retrieveLine(
            int ID) throws DataServiceException, EntityException {
        ResultSet rs = null;
        String sql = "";
        Line res = null;
        String err;

        try {
            sql = "SELECT * from Lineas WHERE IDLinea = " + ID +
                    " AND Borrado = FALSE";
            rs =
                    db.executeQuery(sql);

            if (rs.next()) {
                res = new Line();
                res.setID(rs.getLong(1));
                res.setDescripcion(rs.getString(2));
                res.setFechaAlta(DateTime.DateToCalendar(rs.getDate(3)));
                res.setFechaBaja(DateTime.DateToCalendar(rs.getDate(4)));
                res.setFechaDisponibilidadLinea(DateTime.DateToCalendar(rs.getDate(5)));
                if (rs.next()) {
                    //This condition should never occur, since persistence medium
                    //is responsible of maintaining uniqueness of keys.
                    err = "Existe más de un registro con el ID " + ID + ".";
                    Logger.getLogger("logger").log(Level.SEVERE, err);
                    throw new DataServiceException(err);
                }

            } else {
                throw new DataServiceException("No existe ningún equipo con el ID " + ID + ".");
            }

        } catch (EntityException ex) {
            err = "Error al crear el objeto Equipo con ID " + ID + ".";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        } catch (SQLException ex) {
            err = "Error al recuperar registro de Equipo con ID " + ID + ".";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        }

        return res;
    }

    public synchronized List<Line> retrieveLines() throws DataServiceException, EntityException {
        ResultSet rs = null;
        String sql = "";
        List res = null;
        String err;

        try {

            sql = "SELECT * from lineas WHERE borrado = FALSE";
            rs = db.executeQuery(sql);
            res = makeListOfLines(rs);
        } catch (DataServiceException ex) {
            err = "Error en la conexión con la base de datos.";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        } catch (EntityException ex) {
            err = "Error al crear los objetos Linea.";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        }

        return res;
    }

    public synchronized Map<Long, String> retrieveLinesIDs() throws DataServiceException {
        ResultSet rs = null;
        String sql = "";
        Map res = null;
        String err;

        try {
            sql = "SELECT * from lineas WHERE Borrado = FALSE";
            rs =
                    db.executeQuery(sql);
            res =
                    new HashMap<Long, String>();

            while (rs.next()) {
                res.put(rs.getLong(1), rs.getString(2));
            }

        } catch (SQLException ex) {
            err = "Error al recuperar los registros de Equipos .";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        } finally {
            err = "Error al cerrar los recursos.";
            if (rs != null) {
                try {
                    rs.close();
                    rs =
                            null;
                } catch (SQLException ex) {
                    Logger.getLogger("logger").log(Level.WARNING, err, ex);
                }

            }
        }

        return res;
    }

    public synchronized Map<Long, Line> retrieveLinesLike(String description) throws DataServiceException, EntityException {
        ResultSet rs = null;
        String sql;

        Map res = null;
        Line l = null;
        String err;

        try {
            sql = "SELECT * from Lineas WHERE Descripcion LIKE '%" + description + "%'" +
                    " AND Borrado = FALSE";
            rs =
                    db.executeQuery(sql);
            res =
                    new HashMap<Long, Equipment>();

            while (rs.next()) {
                l = new Line();
                l.setID(rs.getLong(1));
                l.setDescripcion(rs.getString(2));
                l.setFechaAlta(DateTime.DateToCalendar(rs.getDate(3)));
                l.setFechaBaja(DateTime.DateToCalendar(rs.getDate(4)));
                l.setFechaDisponibilidadLinea(DateTime.DateToCalendar(rs.getDate(5)));
                res.put(l.getID(), l);
            }

        } catch (EntityException ex) {
            err = "Error al crear los objetos Equipo.";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        } catch (SQLException ex) {
            err = "Error al recuperar los registros de Equipos .";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        } finally {
            err = "Error al cerrar los recursos.";
            if (rs != null) {
                try {
                    rs.close();
                    rs =
                            null;
                } catch (SQLException ex) {
                    Logger.getLogger("logger").log(Level.WARNING, err, ex);
                }

            }
        }

        return res;
    }

    public synchronized List<Line> retrieveLinesInRange(DataServiceSearchCriterias dateCriterion, Calendar start, Calendar end) throws DataServiceException, EntityException {
        ResultSet rs = null;
        String sql = "";
        List res = null;
        String err;

        if (!(dateCriterion.equals(DataServiceSearchCriterias.CREATION_DATES) ||
                dateCriterion.equals(DataServiceSearchCriterias.DELETION_DATES) ||
                dateCriterion.equals(DataServiceSearchCriterias.AVAILABLE_DATES))) {
            throw new DataServiceException("Incorrect search criterion: " +
                    dateCriterion);
        }

        try {
            sql = "SELECT * from Lineas WHERE " +
                    dateCriterion.toString() + " >= " + DateTime.toSql(start) + " AND " +
                    dateCriterion.toString() + " <= " + DateTime.toSql(end) +
                    " AND Borrado = FALSE";
            rs =
                    db.executeQuery(sql);
            res =
                    makeListOfLines(rs);
        } catch (DataServiceException ex) {
            err = "Error en la conexión con la base de datos.";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        } catch (EntityException ex) {
            err = "Error al crear los objetos Equipo.";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        } finally {
            err = "Error al cerrar los recursos.";
            if (rs != null) {
                try {
                    rs.close();
                    rs =
                            null;
                } catch (SQLException ex) {
                    Logger.getLogger("logger").log(Level.WARNING, err, ex);
                }

            }
        }

        return res;
    }

    public synchronized List<Line> retrieveLinesInRange(DataServiceSearchCriterias dateCriterion1, Calendar start1, Calendar end1, DataServiceSearchCriterias dateCriterion2, Calendar start2, Calendar end2) throws DataServiceException, EntityException {
        ResultSet rs = null;
        String sql = "";
        List res = null;
        String err;

        if (!(dateCriterion1.equals(DataServiceSearchCriterias.CREATION_DATES) ||
                dateCriterion1.equals(DataServiceSearchCriterias.DELETION_DATES) ||
                dateCriterion1.equals(DataServiceSearchCriterias.AVAILABLE_DATES)) &&
                !(dateCriterion2.equals(DataServiceSearchCriterias.CREATION_DATES) ||
                dateCriterion2.equals(DataServiceSearchCriterias.DELETION_DATES) ||
                dateCriterion2.equals(DataServiceSearchCriterias.AVAILABLE_DATES))) {
            throw new DataServiceException("Incorrect search criterion: " +
                    dateCriterion1 + ", " + dateCriterion2);
        }

        try {
            sql = "SELECT * from lineas WHERE " +
                    dateCriterion1.toString() + " >= " + DateTime.toSql(start1) + " AND " +
                    dateCriterion1.toString() + " <= " + DateTime.toSql(end1) + " AND " +
                    dateCriterion2.toString() + " >= " + DateTime.toSql(start2) + " AND " +
                    dateCriterion2.toString() + " <= " + DateTime.toSql(end2) +
                    " AND Borrado = FALSE";
            rs =
                    db.executeQuery(sql);
            res =
                    makeListOfLines(rs);
        } catch (DataServiceException ex) {
            err = "Error en la conexión con la base de datos.";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        } catch (EntityException ex) {
            err = "Error al crear los objetos Equipo.";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        } finally {
            err = "Error al cerrar los recursos.";
            if (rs != null) {
                try {
                    rs.close();
                    rs =
                            null;
                } catch (SQLException ex) {
                    Logger.getLogger("logger").log(Level.WARNING, err, ex);
                }

            }
        }

        return res;
    }

    public synchronized List<Line> retrieveLinesInRange(DataServiceSearchCriterias dateCriterion1, Calendar start1, Calendar end1, DataServiceSearchCriterias dateCriterion2, Calendar start2, Calendar end2, DataServiceSearchCriterias dateCriterion3, Calendar start3, Calendar end3) throws DataServiceException, EntityException {
        ResultSet rs = null;
        String sql = "";
        List res = null;
        String err;

        if (!(dateCriterion1.equals(DataServiceSearchCriterias.CREATION_DATES) ||
                dateCriterion1.equals(DataServiceSearchCriterias.DELETION_DATES) ||
                dateCriterion1.equals(DataServiceSearchCriterias.AVAILABLE_DATES)) &&
                !(dateCriterion2.equals(DataServiceSearchCriterias.CREATION_DATES) ||
                dateCriterion2.equals(DataServiceSearchCriterias.DELETION_DATES) ||
                dateCriterion2.equals(DataServiceSearchCriterias.AVAILABLE_DATES)) &&
                !(dateCriterion3.equals(DataServiceSearchCriterias.CREATION_DATES) ||
                dateCriterion3.equals(DataServiceSearchCriterias.DELETION_DATES) ||
                dateCriterion3.equals(DataServiceSearchCriterias.AVAILABLE_DATES))) {
            throw new DataServiceException("Incorrect search criterion: " +
                    dateCriterion1 + ", " +
                    dateCriterion2 + ", " +
                    dateCriterion3);
        }

        try {
            sql = "SELECT * from lineas WHERE " +
                    dateCriterion1.toString() + " >= " + DateTime.toSql(start1) + " AND " +
                    dateCriterion1.toString() + " <= " + DateTime.toSql(end1) + " AND " +
                    dateCriterion2.toString() + " >= " + DateTime.toSql(start2) + " AND " +
                    dateCriterion2.toString() + " <= " + DateTime.toSql(end2) + " AND " +
                    dateCriterion3.toString() + " >= " + DateTime.toSql(start3) + " AND " +
                    dateCriterion3.toString() + " <= " + DateTime.toSql(end3) +
                    " AND Borrado = FALSE";
            rs =
                    db.executeQuery(sql);
            res =
                    makeListOfLines(rs);
        } catch (DataServiceException ex) {
            err = "Error en la conexión con la base de datos.";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        } catch (EntityException ex) {
            err = "Error al crear los objetos Equipo.";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        } finally {
            err = "Error al cerrar los recursos.";
            if (rs != null) {
                try {
                    rs.close();
                    rs =
                            null;
                } catch (SQLException ex) {
                    Logger.getLogger("logger").log(Level.WARNING, err, ex);
                }

            }
        }

        return res;
    }

    public synchronized List<Line> retrieveLinesInRange(DataServiceSearchCriterias dateCriterion1, Calendar start1, Calendar end1, DataServiceSearchCriterias dateCriterion2, Calendar start2, Calendar end2, DataServiceSearchCriterias dateCriterion3, Calendar start3, Calendar end3, DataServiceSearchCriterias dateCriterion4, Calendar start4, Calendar end4) throws DataServiceException, EntityException {
        ResultSet rs = null;
        String sql = "";
        List res = null;
        String err;

        if (!(dateCriterion1.equals(DataServiceSearchCriterias.CREATION_DATES) ||
                dateCriterion1.equals(DataServiceSearchCriterias.DELETION_DATES) ||
                dateCriterion1.equals(DataServiceSearchCriterias.AVAILABLE_DATES)) &&
                !(dateCriterion2.equals(DataServiceSearchCriterias.CREATION_DATES) ||
                dateCriterion2.equals(DataServiceSearchCriterias.DELETION_DATES) ||
                dateCriterion2.equals(DataServiceSearchCriterias.AVAILABLE_DATES)) &&
                !(dateCriterion3.equals(DataServiceSearchCriterias.CREATION_DATES) ||
                dateCriterion3.equals(DataServiceSearchCriterias.DELETION_DATES) ||
                dateCriterion3.equals(DataServiceSearchCriterias.AVAILABLE_DATES)) &&
                !(dateCriterion4.equals(DataServiceSearchCriterias.CREATION_DATES) ||
                dateCriterion4.equals(DataServiceSearchCriterias.DELETION_DATES) ||
                dateCriterion4.equals(DataServiceSearchCriterias.AVAILABLE_DATES))) {
            throw new DataServiceException("Incorrect search criterion: " +
                    dateCriterion1 + ", " +
                    dateCriterion2 + ", " +
                    dateCriterion3 + ", " +
                    dateCriterion4);
        }

        try {
            sql = "SELECT * from lineas WHERE " +
                    dateCriterion1.toString() + " >= " + DateTime.toSql(start1) + " AND " +
                    dateCriterion1.toString() + " <= " + DateTime.toSql(end1) + " AND " +
                    dateCriterion2.toString() + " >= " + DateTime.toSql(start2) + " AND " +
                    dateCriterion2.toString() + " <= " + DateTime.toSql(end2) + " AND " +
                    dateCriterion3.toString() + " >= " + DateTime.toSql(start3) + " AND " +
                    dateCriterion4.toString() + " >= " + DateTime.toSql(start4) + " AND " +
                    dateCriterion4.toString() + " <= " + DateTime.toSql(end4) +
                    " AND Borrado = FALSE";
            rs =
                    db.executeQuery(sql);
            res =
                    makeListOfLines(rs);
        } catch (DataServiceException ex) {
            err = "Error en la conexión con la base de datos.";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        } catch (EntityException ex) {
            err = "Error al crear los objetos Equipo.";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        } finally {
            err = "Error al cerrar los recursos.";
            if (rs != null) {
                try {
                    rs.close();
                    rs =
                            null;
                } catch (SQLException ex) {
                    Logger.getLogger("logger").log(Level.WARNING, err, ex);
                }

            }
        }

        return res;
    }

    public List<Line> retrieveLinesInRange(DataServiceSearchCriterias dateCriterion1, Calendar start1, Calendar end1, DataServiceSearchCriterias dateCriterion2, Calendar start2, Calendar end2, DataServiceSearchCriterias dateCriterion3, Calendar start3, Calendar end3, DataServiceSearchCriterias dateCriterion4, Calendar start4, Calendar end4, DataServiceSearchCriterias dateCriterion5, Calendar start5, Calendar end5) throws DataServiceException, EntityException {
        ResultSet rs = null;
        String sql = "";
        List res = null;
        String err;

        if (!(dateCriterion1.equals(DataServiceSearchCriterias.CREATION_DATES) ||
                dateCriterion1.equals(DataServiceSearchCriterias.DELETION_DATES) ||
                dateCriterion1.equals(DataServiceSearchCriterias.AVAILABLE_DATES)) &&
                !(dateCriterion2.equals(DataServiceSearchCriterias.CREATION_DATES) ||
                dateCriterion2.equals(DataServiceSearchCriterias.DELETION_DATES) ||
                dateCriterion2.equals(DataServiceSearchCriterias.AVAILABLE_DATES)) &&
                !(dateCriterion3.equals(DataServiceSearchCriterias.CREATION_DATES) ||
                dateCriterion3.equals(DataServiceSearchCriterias.DELETION_DATES) ||
                dateCriterion3.equals(DataServiceSearchCriterias.AVAILABLE_DATES)) &&
                !(dateCriterion4.equals(DataServiceSearchCriterias.CREATION_DATES) ||
                dateCriterion4.equals(DataServiceSearchCriterias.DELETION_DATES) ||
                dateCriterion4.equals(DataServiceSearchCriterias.AVAILABLE_DATES)) &&
                !(dateCriterion5.equals(DataServiceSearchCriterias.CREATION_DATES) ||
                dateCriterion5.equals(DataServiceSearchCriterias.DELETION_DATES) ||
                dateCriterion5.equals(DataServiceSearchCriterias.AVAILABLE_DATES))) {
            throw new DataServiceException("Incorrect search criterion: " +
                    dateCriterion1 + ", " +
                    dateCriterion2 + ", " +
                    dateCriterion3 + ", " +
                    dateCriterion4 + ", " +
                    dateCriterion5);
        }

        try {
            sql = "SELECT * from lineas WHERE " +
                    dateCriterion1.toString() + " >= " + DateTime.toSql(start1) + " AND " +
                    dateCriterion1.toString() + " <= " + DateTime.toSql(end1) + " AND " +
                    dateCriterion2.toString() + " >= " + DateTime.toSql(start2) + " AND " +
                    dateCriterion2.toString() + " <= " + DateTime.toSql(end2) + " AND " +
                    dateCriterion3.toString() + " >= " + DateTime.toSql(start3) + " AND " +
                    dateCriterion4.toString() + " >= " + DateTime.toSql(start4) + " AND " +
                    dateCriterion4.toString() + " <= " + DateTime.toSql(end4) + " AND " +
                    dateCriterion5.toString() + " >= " + DateTime.toSql(start5) + " AND " +
                    dateCriterion5.toString() + " <= " + DateTime.toSql(end5) +
                    " AND Borrado = FALSE";
            rs =
                    db.executeQuery(sql);
            res =
                    makeListOfLines(rs);
        } catch (DataServiceException ex) {
            err = "Error en la conexión con la base de datos.";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        } catch (EntityException ex) {
            err = "Error al crear los objetos Equipo.";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        }

        return res;
    }

    public synchronized long saveEquipmentMaintenance(EquipmentMaintenance em) throws DataServiceException {
        String sql = "";
        String err;

        long[] queryRes;

        try {
            if (em.getID() < 0) {
                sql = "INSERT INTO mantenimientosequipos (" +
                        "FechaMaximaMantenimiento, " +
                        "Mantenimientos_idMantenimientos, " +
                        "Equipos_idEquipo) " +
                        "VALUES (" +
                        DateTime.toSql(em.getMaintenanceDate()) + ", " +
                        em.getMaintenanceID() + ", " +
                        em.getEquipmentID() + ")";
                queryRes =
                        db.executeUpdate(sql, true);
            } else {
                sql = "UPDATE mantenimientosequipos SET " +
                        "FechaMaximaMantenimiento = " + DateTime.toSql(em.getMaintenanceDate()) + " " +
                        "Mantenimientos_idMantenimientos = " + em.getMaintenanceID() + " " +
                        "Equipos_idEquipo = " + em.getEquipmentID() + " " +
                        "WHERE idMantenimientosEquipo = " + em.getID();
                queryRes =
                        new long[2];
                queryRes[0] = db.executeUpdate(sql);
                queryRes[1] = em.getID();
            }

            if (queryRes[0] != 1) {
                if (queryRes[0] == 0) {
                    Logger.getLogger("logger").log(Level.WARNING, "Cero registros agregados/modificados.", sql);
                } else {
                    //This condition should never occur, since persistence medium
                    //is responsible of maintaining uniqueness of keys.
                    Logger.getLogger("logger").log(Level.SEVERE, "Más de un registros fue agregado/modificado.", sql);
                }

                throw new DataServiceException("Se insertaron/modificaron " + queryRes[0] + " registros.");
            }

        } catch (DataServiceException ex) {
            err = "Error al grabar registro de mantenimiento a equipo.";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        }

        return queryRes[1];
    }

    public synchronized void deleteEquipmentMaintenance(long ID) throws DataServiceException {
        String sql;
        String err;

        int queryRes;

        try {
            sql = "UPDATE mantenimientosequipos SET Borrado = TRUE WHERE idMantenimientosEquipo = " + ID;
            queryRes =
                    db.executeUpdate(sql);

            if (queryRes == 0) {
                throw new DataServiceException("Ningún registro fue marcado como borrado.");
            } else {
                //This condition should never occur, since persistence medium
                //is responsible of maintaining uniqueness of keys.
                err = "Más de un registro fue marcado como borrado.";
                Logger.getLogger("logger").log(Level.SEVERE, err, sql);
                throw new DataServiceException(err);
            }

        } catch (DataServiceException ex) {
            err = "Error al marcar como borrado el registro con ID " + ID + ".";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        }

    }

    public synchronized void undeleteEquipmentMaintenance(long ID) throws DataServiceException {
        String sql;
        String err;

        int queryRes;

        try {
            sql = "UPDATE mantenimientosequipos SET Borrado = FALSE WHERE idMantenimientosEquipo = " + ID;
            queryRes =
                    db.executeUpdate(sql);

            if (queryRes == 0) {
                throw new DataServiceException("Ningún registro fue desmarcado como borrado.");
            } else {
                //This condition should never occur, since persistence medium
                //is responsible of maintaining uniqueness of keys.
                err = "Más de un registro fue desmarcado como borrado.";
                Logger.getLogger("logger").log(Level.SEVERE, err, sql);
                throw new DataServiceException(err);
            }

        } catch (DataServiceException ex) {
            err = "Error al desmarcar como borrado el registro con ID " + ID + ".";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        }

    }

    public synchronized EquipmentMaintenance retrieveEquipmentMaintenance(
            long ID) throws DataServiceException, EntityException {
        ResultSet rs = null;
        String sql = "";
        EquipmentMaintenance res = null;
        String err;

        try {
            sql = "SELECT * from mantenimientosequipos WHERE idMantenimientosEquipo = " + ID +
                    " AND Borrado = FALSE";
            rs =
                    db.executeQuery(sql);

            if (rs.next()) {
                res = new EquipmentMaintenance();
                res.setID(rs.getLong(1));
                res.setMaintenanceDate(DateTime.DateToCalendar(rs.getDate(2)));
                res.setMaintenanceID(rs.getLong(3));
                res.setEquipmentID(rs.getLong(4));
                if (rs.next()) {
                    //This condition should never occur, since persistence medium
                    //is responsible of maintaining uniqueness of keys.
                    err = "Existe más de un registro con el ID " + ID + ".";
                    Logger.getLogger("logger").log(Level.SEVERE, err);
                    throw new DataServiceException(err);
                }

            } else {
                throw new DataServiceException("No existe ningún mantenimiento a equipo con el ID " + ID + ".");
            }

        } catch (EntityException ex) {
            err = "Error al crear el objeto Mantenimiento a Equipo con ID " + ID + ".";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        } catch (SQLException ex) {
            err = "Error al recuperar registro de Mantenimiento a Equipo con ID " + ID + ".";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        }

        return res;
    }

    public synchronized List<EquipmentMaintenance> retrieveEquipmentLog(long equipmentID) throws DataServiceException, EntityException {
        ResultSet rs = null;
        EquipmentMaintenance em = null;
        String sql = "";
        List<EquipmentMaintenance> res = new ArrayList();
        String err;

        try {
            sql = "SELECT * from mantenimientosequipos " +
                    "WHERE Equipos_idEquipo = " + equipmentID +
                    " AND Borrado = FALSE";
            rs = db.executeQuery(sql);
            while (rs.next()) {
                em = new EquipmentMaintenance();
                em.setID(rs.getLong(1));
                em.setMaintenanceDate(DateTime.DateToCalendar(rs.getDate(2)));
                em.setMaintenanceID(rs.getLong(3));
                em.setEquipmentID(rs.getLong(4));
                res.add(em);
            }

        } catch (SQLException ex) {
            err = "Error en la conexión con la base de datos.";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        } catch (EntityException ex) {
            err = "Error al crear los objetos Mantenimiento.";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        }

        return res;
    }

    public synchronized Map<Long, List<EquipmentMaintenance>> retrieveLog() throws DataServiceException, EntityException {
        ResultSet rsGen = null;
        ResultSet rs = null;
        Map<Long, List<EquipmentMaintenance>> res = new HashMap<Long, List<EquipmentMaintenance>>();
        List<EquipmentMaintenance> equipmentLog = null;
        EquipmentMaintenance em = null;
        String sql = "";
        String err;

        try {
            sql = "SELECT DISTINCT Equipos_idEquipo from mantenimientosequipos " +
                    "WHERE Borrado = FALSE";
            rsGen = db.executeQuery(sql);
            long id;
            while (rsGen.next())
            {   id= rsGen.getLong(1) ;
                sql = "SELECT * from mantenimientosequipos " +
                        "WHERE Equipos_idEquipo = " + id +
                        " AND Borrado = FALSE";
                rs = db.executeQuery(sql);
                equipmentLog = new ArrayList<EquipmentMaintenance>();
                while (rs.next()) {
                    em = new EquipmentMaintenance();
                    em.setID(rs.getLong(1));
                    em.setMaintenanceDate(DateTime.DateToCalendar(rs.getDate(2)));
                    em.setMaintenanceID(rs.getLong(3));
                    em.setEquipmentID(rs.getLong(4));
                    equipmentLog.add(em);
                }

                res.put(id, equipmentLog);
            }

        } catch (SQLException ex) {
            err = "Error en la conexión con la base de datos.";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        } catch (EntityException ex) {
            err = "Error al crear los objetos Mantenimiento.";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        }

        return res;
    }

    public synchronized long saveProgrammedMaintenance(ProgrammedMaintenance pm) throws DataServiceException {
        String sql = "";
        String err;

        long[] queryRes;

        try {
            if (pm.getID() < 0) {
                sql = "INSERT INTO mantenimientosprogramados (" +
                        "FechaInicioMantenimiento, " +
                        "Lineas_idLineas, " +
                        "MantenimientosEquipos_idMantenimientosEquipo) " +
                        "VALUES (" +
                        DateTime.toSql(pm.getStartDate().getTime()) + ", " +
                        pm.getLineID() + ", " +
                        pm.getEquipmentMaintenanceID() + ")";
                queryRes = db.executeUpdate(sql, true);
            } else {
                sql = "UPDATE mantenimientosprogramados SET " +
                        "FechaInicioMantenimiento = " + DateTime.toSql(pm.getStartDate().getTime()) + " ," +
                        "Mantenimientos_idMantenimientos = " + pm.getLineID() + " ," +
                        "MantenimientosEquipos_idMantenimientosEquipo = " + pm.getEquipmentMaintenanceID() + " " +
                        "WHERE idMantenimientoProgramado =" + pm.getID();
                queryRes =
                        new long[2];
                queryRes[0] = db.executeUpdate(sql);
                queryRes[1] = pm.getID();
            }

            if (queryRes[0] != 1) {
                if (queryRes[0] == 0) {
                    Logger.getLogger("logger").log(Level.WARNING, "Cero registros agregados/modificados.", sql);
                } else {
                    //This condition should never occur, since persistence medium
                    //is responsible of maintaining uniqueness of keys.
                    Logger.getLogger("logger").log(Level.SEVERE, "Más de un registros fue agregado/modificado.", sql);
                }

                throw new DataServiceException("Se insertaron/modificaron " + queryRes[0] + " registros.");
            }

        } catch (DataServiceException ex) {
            err = "Error al grabar registro de mantenimiento programado.";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        }

        return queryRes[1];
    }

        public synchronized String getLines() throws SQLException {
        String ret = "POIT";
        try {
            
            ProgrammedMaintenance res;
            List<Line> lst = instance.retrieveLines();
            for (int i = 0; i < lst.size(); i++) {
                Long id = lst.get(i).getID();
                String sql = "Select * from mantenimientosprogramados where id=" + id + "";
                ResultSet rs = db.executeQuery(sql);
                while (rs.next()) {
                    res = new ProgrammedMaintenance();
                    res.setID(rs.getLong(1));
                    res.setStartDate(DateTime.DateToCalendar(rs.getDate(2)));
                    res.setEquipmentMaintenanceID(rs.getLong(3));
                    res.setLineID(rs.getLong(4));
                    ret +=  res.getID() + " " + res.getStartDate() + " " + res.getEquipmentMaintenanceID() + " " + res.getLineID();
                    
                }
            }
        } catch (DataServiceException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } catch (EntityException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ret;
   }
    
    public synchronized void deleteProgrammedMaintenance(long ID) throws DataServiceException {
        String sql;
        String err;

        int queryRes;

        try {
            sql = "UPDATE mantenimientosprogramados SET Borrado = TRUE WHERE idMantenimientoProgramado = " + ID;
            queryRes =
                    db.executeUpdate(sql);

            if (queryRes == 0) {
                throw new DataServiceException("Ningún registro fue marcado como borrado.");
            } else {
                //This condition should never occur, since persistence medium
                //is responsible of maintaining uniqueness of keys.
                err = "Más de un registro fue marcado como borrado.";
                Logger.getLogger("logger").log(Level.SEVERE, err, sql);
                throw new DataServiceException(err);
            }

        } catch (DataServiceException ex) {
            err = "Error al marcar como borrado el registro con ID " + ID + ".";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        }

    }

    public synchronized void undeleteProgrammedMaintenance(long ID) throws DataServiceException {
        String sql;
        String err;

        int queryRes;

        try {
            sql = "UPDATE mantenimientosprogramados SET Borrado = FALSE WHERE idMantenimientoProgramado = " + ID;
            queryRes =
                    db.executeUpdate(sql);

            if (queryRes == 0) {
                throw new DataServiceException("Ningún registro fue desmarcado como borrado.");
            } else {
                //This condition should never occur, since persistence medium
                //is responsible of maintaining uniqueness of keys.
                err = "Más de un registro fue desmarcado como borrado.";
                Logger.getLogger("logger").log(Level.SEVERE, err, sql);
                throw new DataServiceException(err);
            }

        } catch (DataServiceException ex) {
            err = "Error al desmarcar como borrado el registro con ID " + ID + ".";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        }

    }

    public synchronized ProgrammedMaintenance retrieveProgrammedMaintenance(
            long ID) throws DataServiceException, EntityException {
        ResultSet rs = null;
        String sql = "";
        ProgrammedMaintenance res = null;
        String err;

        try {
            sql = "SELECT * from mantenimientosprogramados WHERE idMantenimientoProgramado = " + ID +
                    " AND Borrado = FALSE";
            rs =
                    db.executeQuery(sql);

            if (rs.next()) {
                res = new ProgrammedMaintenance();
                res.setID(rs.getLong(1));
                res.setStartDate(DateTime.DateToCalendar(rs.getDate(2)));
                res.setLineID(rs.getLong(3));
                res.setEquipmentMaintenanceID(rs.getLong(4));
                if (rs.next()) {
                    //This condition should never occur, since persistence medium
                    //is responsible of maintaining uniqueness of keys.
                    err = "Existe más de un registro con el ID " + ID + ".";
                    Logger.getLogger("logger").log(Level.SEVERE, err);
                    throw new DataServiceException(err);
                }

            } else {
                throw new DataServiceException("No existe ningún mantenimiento programado con el ID " + ID + ".");
            }

        } catch (EntityException ex) {
            err = "Error al crear el objeto Mantenimiento Programado con ID " + ID + ".";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        } catch (SQLException ex) {
            err = "Error al recuperar registro de Mantenimiento Programado con ID " + ID + ".";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        }

        return res;
    }

    public synchronized List<ProgrammedMaintenance> retrieveEquipmentProgram(long equipmentID) throws DataServiceException, EntityException {
        ResultSet rs = null;
        ProgrammedMaintenance pm = null;
        String sql = "";
        List res = null;
        String err;

        try {
            sql = "SELECT idMantenimientoProgramado, FechaInicioMantenimiento, " +
                    "Lineas_idLineas, MantenimientosEquipos_idMantenimientosEquipo " +
                    "FROM mantenimientosprogramados mp, mantenimientosequipos me " +
                    "WHERE mp.MantenimientosEquipos_idMantenimientosEquipo = " +
                    "me.idMantenimientosEquipo" +
                    " AND Equipos_idEquipo = " + equipmentID +
                    " AND mp.Borrado = FALSE";
            rs =
                    db.executeQuery(sql);
            res =
                    new ArrayList<EquipmentMaintenance>();
            while (rs.next()) {
                pm = new ProgrammedMaintenance();
                pm.setID(rs.getLong(1));
                pm.setStartDate(DateTime.DateToCalendar(rs.getDate(2)));
                pm.setLineID(rs.getLong(3));
                pm.setEquipmentMaintenanceID(rs.getLong(4));
                res.add(pm);
            }

        } catch (SQLException ex) {
            err = "Error en la conexión con la base de datos.";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        } catch (EntityException ex) {
            err = "Error al crear los objetos Mantenimiento.";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        }

        return res;
    }

    public synchronized Map<Long, List<ProgrammedMaintenance>> retrieveProgram() throws DataServiceException, EntityException {
        ResultSet rsGen = null;
        ResultSet rs = null;
        Map res = null;
        List<ProgrammedMaintenance> equipmentProgram = null;
        ProgrammedMaintenance pm = null;
        String sql = "";
        String err;

        try {
            sql = "SELECT DISTINCT Equipos_idEquipo " +
                    "FROM mantenimientosprogramados mp, mantenimientosequipos me " +
                    "WHERE mp.MantenimientosEquipos_idMantenimientosEquipo = " +
                    "me.idMantenimientosEquipo" +
                    " AND mp.Borrado = FALSE";
            rsGen =
                    db.executeQuery(sql);
            while (rsGen.next()) {
                sql = "SELECT idMantenimientoProgramado, FechaInicioMantenimiento, " +
                        "Lineas_idLineas, MantenimientosEquipos_idMantenimientosEquipo " +
                        "FROM mantenimientosprogramados mp, mantenimientosequipos me " +
                        "WHERE mp.MantenimientosEquipos_idMantenimientosEquipo = " +
                        "me.idMantenimientosEquipo" +
                        " AND Equipos_idEquipo = " + rsGen.getLong(1) +
                        " AND mp.Borrado = FALSE";
                rs =
                        db.executeQuery(sql);
                equipmentProgram =
                        new ArrayList<ProgrammedMaintenance>();
                while (rs.next()) {
                    pm = new ProgrammedMaintenance();
                    pm.setID(rs.getLong(1));
                    pm.setStartDate(DateTime.DateToCalendar(rs.getDate(2)));
                    pm.setLineID(rs.getLong(3));
                    pm.setEquipmentMaintenanceID(rs.getLong(4));
                    equipmentProgram.add(pm);
                }

                res.put(rsGen.getLong(1), equipmentProgram);
            }

        } catch (SQLException ex) {
            err = "Error en la conexión con la base de datos.";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        } catch (EntityException ex) {
            err = "Error al crear los objetos Mantenimiento Programado.";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        }

        return res;
    }

    public synchronized void deleteAllPrograms() throws DataServiceException {
        String sql;
        String err;

        int queryRes;

        try {
            sql = "DELETE FROM mantenimientosprogramados";
            queryRes = db.executeUpdate(sql);

        } catch (DataServiceException ex) {
            err = "Error al eliminar los registros de la tabla mantenimientosprogramados";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        }
    }

    public void purge() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public List<?> retrieveDeletedObjects(Object sample) throws DataServiceException, EntityException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    private synchronized List<Equipment> makeListOfEquipments(ResultSet rs) throws DataServiceException, EntityException {
        List<Equipment> res = new ArrayList();
        Equipment e = null;
        String err;

        try {
            while (rs.next()) {
                e = new Equipment();
                e.setID(rs.getLong(1));
                System.out.println("-------------------------------");
                System.out.println(rs.getString(2));
                System.out.println("-------------------------------");
                e.setTailNumber(rs.getString(2));
                e.setDisponible(rs.getBoolean(6));
                e.setFechaAlta(DateTime.DateToCalendar(rs.getDate(3)));
                e.setFechaDisponibilidad(DateTime.DateToCalendar(rs.getDate(5)));
                e.setFechaBaja(DateTime.DateToCalendar(rs.getDate(4)));
                res.add(e);
            }

        } catch (EntityException ex) {
            err = "Error al crear los objetos Equipo.";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        } catch (SQLException ex) {
            err = "Error al recuperar los registros de Equipos .";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        }

        return res;
    }

    private List<Line> makeListOfLines(ResultSet rs) throws DataServiceException, EntityException {
        List<Line> res = new ArrayList();
        Line l = null;
        String err;

        try {
            while (rs.next()) {
                l = new Line();
                l.setID(rs.getLong(1));
                l.setDescripcion(rs.getString(2));
                l.setFechaAlta(DateTime.DateToCalendar(rs.getDate(3)));
                l.setFechaBaja(DateTime.DateToCalendar(rs.getDate(4)));
                l.setFechaDisponibilidadLinea(DateTime.DateToCalendar(rs.getDate(5)));
                res.add(l);
            }
        } catch (EntityException ex) {
            err = "Error al crear los objetos Lineas.";
            Logger.getLogger("logger").log(Level.SEVERE, err, ex);
            throw new EntityException(err);
        } catch (SQLException ex) {
            err = "Error al recuperar los registros de Lineas .";
            Logger.getLogger("logger").log(Level.WARNING, err, ex);
            throw new DataServiceException(err);
        }

        return res;
    }
}
